/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mvc.model;

/**
 *
 * @author Alex
 */
import com.gesturn2.comercio.Empresa;
import com.gesturn2.comercio.Punto_de_Vigilancia;
import com.gesturn2.comercio.Sede;

import com.gesturn2.comercio.Servicio;
import java.io.*;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;


public class Model implements Serializable{

    protected static Connection con;
    private String jdbcDriver;
    private String jdbcUrl;
    private String user;
    private String password;
    private String database;
        public static Connection getCon() {
 return con;
    }

    public String getJdbcDriver() {
        return jdbcDriver;
    }

    public void setJdbcDriver(String jdbcDriver) {
        this.jdbcDriver = jdbcDriver;
    }

    public String getJdbcUrl() {
        return jdbcUrl;
    }

    public void setJdbcUrl(String jdbcUrl) {
        this.jdbcUrl = jdbcUrl;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDatabase() {
        return database;
    }

    public void setDatabase(String database) {
        this.database = database;
    }
    
    public void conectar() throws SQLException {
        try {
            Class.forName(this.jdbcDriver);
        } catch (ClassNotFoundException ex) {
            throw new SQLException("Driver no cargado !!!"+ex.toString());
        }
        System.out.println("------------Conexion Driver--------- :"+this.getJdbcUrl()+this.getDatabase()+","+this.getUser()+","+this.getPassword());
        con = DriverManager.getConnection(this.getJdbcUrl()+this.getDatabase(), this.getUser(), this.getPassword());
    }
    public boolean isConnect()
    {
        if(con!=null)
        {
           return true;
        }else
        {
           return false;
        }
    }
    public void disconnect()
    {
        if(con!=null)
        {
            try
            {
              con.close();
            }catch(SQLException ignore){}
            finally
            {
              con=null;
            }
        }
    }




  
  public void agregarServicio(Servicio per)throws SQLException{
               PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO servicios VALUES (?,?,?,?)");

			pstmt.setString(1,per.getCodigo());
			pstmt.setString(2,per.getNombre());
			pstmt.setDouble(3,per.getValor());
			pstmt.setString(4,per.getDescripcion());

			pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }

  public int ultimoIDdireccioningresado()throws SQLException{
      PreparedStatement psInsert;
      List<UbicacionDireccion>listadirecciones=new LinkedList<UbicacionDireccion>();
      UbicacionDireccion ubi=new UbicacionDireccion();
      String sqlListar="Select id from direcciones";
               psInsert = con.prepareStatement(sqlListar);
                ResultSet r= psInsert.executeQuery();
                while(r.next()){
           UbicacionDireccion u= new UbicacionDireccion();
           u.setId(r.getInt("id"));
           listadirecciones.add(u);
                 }
                int tamañoLista=listadirecciones.size();
                ubi=listadirecciones.get(tamañoLista-1);
                int x=ubi.getId();

		return  x;



  }
  public boolean existServicio(Servicio per) throws SQLException{

		boolean sw=true;
		PreparedStatement pstmt=null;
		ResultSet rs=null;

		try{
			pstmt=con.prepareStatement("SELECT COUNT(*) FROM servicios WHERE codigo=?");
			pstmt.setString(1, per.getCodigo());
			rs=pstmt.executeQuery();
			if(rs.next())
				if(rs.getString(1).equals("0"))
					sw=false;
		}finally{
			if(rs!=null)
				rs.close();

			if(pstmt!=null)
				pstmt.close();
		}

		return sw;
	}
  public void agregarContacto(Contacto per)throws SQLException{
               PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO contactos VALUES (?,?,?,?,?,?)");
                        pstmt.setString(1,per.getCedula());
			pstmt.setString(2,per.getNombre());
			pstmt.setString(3,per.getApellido());
			pstmt.setString(4,per.getCargo());
			pstmt.setString(5,per.getTelefono());
                        pstmt.setString(6,per.getE_mail());

			pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }
  public Contacto consultarContacto(String per) throws SQLException{

		if (!isConnect())
		throw new SQLException("no hay conexcion");
        String sql="Select * from contactos where cedula = ?";
        PreparedStatement ps=Model.getCon().prepareStatement(sql);
        ps.setString(1, per);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
        Contacto emp=new Contacto();
        emp.setCedula(rs.getString("cedula"));
        emp.setNombre(rs.getString("nombre"));
        emp.setApellido(rs.getString("apellido"));
        emp.setCargo(rs.getString("cargo"));
        emp.setTelefono(rs.getString("telefono"));
        emp.setE_mail(rs.getString("email"));

        return emp;

        }
        return null;
    }
   public void agregarEmpresa(Empresa per)throws SQLException{
               PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO empresas VALUES (?,?,?,?,?,?)");
                        pstmt.setString(1,per.getIdentificacion());
			pstmt.setString(2,per.getNombre());
			pstmt.setInt(3,per.getDireccion().getId());
			pstmt.setString(4,per.getE_mail());
                        pstmt.setString(5,per.getTelefono());
                        pstmt.setString(6,per.getContacto().getCedula());


                        



			pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }

      
    public void agregarDireccion(UbicacionDireccion per)throws SQLException{
    PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO direcciones VALUES (?,?,?,?,?,?)");
                        pstmt.setInt(1,per.getId());
                         pstmt.setString(2,per.getBarrio());
                          pstmt.setString(3, per.getUrbanizacion());
                          pstmt.setString(4, per.getCiudad());
                         pstmt.setString(5, per.getEstado());
                         pstmt.setString(6, per.getPai());

                        pstmt.executeUpdate();
        }finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

    }
    public List listarEmpresas() throws SQLException {
        if (!isConnect())
		throw new SQLException("no hay conexcion");

        PreparedStatement ps = null;
        ResultSet rs = null;
        Empresa c = null;
        List lista = new LinkedList();
        try {
            ps =(PreparedStatement)  con.prepareStatement("select * from empresas ");
            rs = ps.executeQuery();

            while (rs.next()) {
                c = this.loadEmpresas(rs);
                lista.add(c);
            }

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return lista;
    }

     private Empresa loadEmpresas(ResultSet rs) throws SQLException {
        Empresa c = new Empresa();

        c.setIdentificacion(rs.getString(1));
        c.setNombre(rs.getString(2));
        c.setDireccion(null);
        c.setE_mail(rs.getString(4));
        c.setTelefono(rs.getString(5));
        c.setContacto(null);

        return c;
    }

      public void agregarSede(Sede per)throws SQLException{
               PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO sedes VALUES (?,?,?,?)");
                        pstmt.setString(1,per.getId());
			pstmt.setString(2,per.getNombre());
			pstmt.setString(3,per.getDireccion());
			pstmt.setString(4,per.getEmpresa().getIdentificacion());

                        pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }
      public Object extraeSede (Object id) throws SQLException{
          String id1=(String)id;
                    Connection conex=getCon();

                if (!isConnect())
			throw new SQLException("no hay conexcion");
                Sede u= new Sede();
		PreparedStatement pstmt=null;
		ResultSet rs=null;

		try{
			pstmt=conex.prepareStatement("SELECT * FROM sedes WHERE id=?");
			pstmt.setString(1,id1);
			rs=pstmt.executeQuery();
			if(rs.next()){
			  u=(Sede)loadS(rs);

                        }else{

                        }
		}finally{
			if(rs!=null)
				rs.close();

			if(pstmt!=null)
				pstmt.close();
		}

		return u;
     }
       public Object loadS(ResultSet rs) throws SQLException {

        Sede u = new Sede();
        u.setId(rs.getString(1));
        u.setNombre(rs.getString(2));
        u.setDireccion(rs.getString(3));
        Empresa empresa=consultarEmpresa(rs.getString(4));
        u.setEmpresa(empresa);


        return u;
    }

       public List listarSede() throws SQLException {
        if (!isConnect())
		throw new SQLException("no hay conexcion");

        PreparedStatement ps = null;
        ResultSet rs = null;
        Sede c = null;
        List lista = new LinkedList();
        try {
            ps =(PreparedStatement)  con.prepareStatement("select * from sedes ");
            rs = ps.executeQuery();

            while (rs.next()) {
                c = (Sede) this.loadS(rs);
                lista.add(c);
            }

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return lista;
    }
      public Empresa consultarEmpresa(String per) throws SQLException{
        Model m=new Model();
        Contacto c=new Contacto();
		if (!isConnect())
		throw new SQLException("no hay conexcion");
        String sql="Select * from empresas where identificacion = ?";
        PreparedStatement ps=Model.getCon().prepareStatement(sql);
        ps.setString(1, per);
        ResultSet rs=ps.executeQuery();
       
        Empresa emp=new Empresa();
        
        while(rs.next()){
         emp.setIdentificacion(rs.getString("identificacion"));
        emp.setNombre(rs.getString("nombre"));
        emp.setE_mail(rs.getString("email"));
        emp.setTelefono(rs.getString("telefono"));
        c= m.consultarContacto(rs.getString("codigo_contaco"));
        
        emp.setContacto(c);
        return emp;

        }
        return null;
    }
     public List listarPuntos_de_vigilancia() throws SQLException {
        if (!isConnect())
		throw new SQLException("no hay conexcion");

        PreparedStatement ps = null;
        ResultSet rs = null;
        Punto_de_Vigilancia c = null;
        List lista = new LinkedList();
        try {
            ps =(PreparedStatement)  con.prepareStatement("select * from puntos_de_vigilancia ");
            rs = ps.executeQuery();

            while (rs.next()) {
                c = this.loadPuntos_de_vigilancia(rs);
                lista.add(c);
            }

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return lista;
    }
	 private Punto_de_Vigilancia loadPuntos_de_vigilancia(ResultSet rs) throws SQLException {
        Punto_de_Vigilancia c = new Punto_de_Vigilancia();

        c.setId(rs.getString(1));
        c.setNombre(rs.getString(2));
        Object o=extraeSede(rs.getString(3));
         Sede sede=(Sede)o;
        c.setSede(sede);
        Servicio s=consultarServicio(rs.getString(4));
        c.setServicio(s);

        return c;
    }
          public Punto_de_Vigilancia consultarPuntoDeVigilancia(String per) throws SQLException{

		if (!isConnect())
		throw new SQLException("no hay conexcion");
        String sql="Select * from puntos_de_vigilancia where codigo = ?";
        PreparedStatement ps=Model.getCon().prepareStatement(sql);
        ps.setString(1, per);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
        Punto_de_Vigilancia emp=new Punto_de_Vigilancia();
        emp.setId(rs.getString("codigo"));
        emp.setNombre(rs.getString("nombre"));
        return emp;

        }
        return null;
    }
          public void agregarPunto(Punto_de_Vigilancia per)throws SQLException{
               PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO puntos_de_vigilancia VALUES (?,?,?,?)");
                        pstmt.setString(1,per.getId());
			pstmt.setString(2,per.getNombre());
			pstmt.setString(3,per.getSede().getId());
			pstmt.setString(4,per.getServicio().getCodigo());

                        pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }
          public List listarServicios() throws SQLException {
        if (!isConnect())
		throw new SQLException("no hay conexcion");

        PreparedStatement ps = null;
        ResultSet rs = null;
        Servicio c = null;
        List lista = new LinkedList();
        try {
            ps =(PreparedStatement)  con.prepareStatement("select * from servicios ");
            rs = ps.executeQuery();

            while (rs.next()) {
                c = this.loadServicio(rs);
                lista.add(c);
            }

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return lista;
    }
 private Servicio loadServicio(ResultSet rs) throws SQLException {
        Servicio c = new Servicio();

        c.setCodigo(rs.getString(1));
        c.setNombre(rs.getString(2));
        c.setValor(rs.getDouble(3));



        return c;
    }
 public Servicio consultarServicio(String per) throws SQLException{

		if (!isConnect())
		throw new SQLException("no hay conexcion");
        String sql="Select * from servicios where codigo = ?";
        PreparedStatement ps=Model.getCon().prepareStatement(sql);
        ps.setString(1, per);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
        Servicio emp=new Servicio();
        emp.setCodigo(rs.getString(1));
        emp.setNombre(rs.getString(2));
        emp.setValor(rs.getDouble(3));
        emp.setDescripcion(rs.getString(4));
        return emp;

        }
        return null;
}
}
